#!/usr/bin/perl -w
use strict;
use Includes;
use CGI qw/:standard *table *div -nosticky/;
use CGI::Pretty qw/ :html3 /;
use DBD::SQLite;

# Some variables
my $c = new CGI; $c->autoEscape(undef);
my $SUCCESS = 0;

# Some paths
my $DBFILE	 = "projects.sql";

# Make the basic HTML and form 
print header(-type=>'text/html'),
			Includes::get_html('Projects'),
			div({id=>'main',class=>'rounded'},
					Includes::get_banner(),
					div({id=>'projects'},
						&filter,
						&results)),
			end_html();

# Check to see if we succeeded 
my $username = $c->cookie('username') || $c->param('username') || 'CHANGE_ME';
if($SUCCESS == 1 && $username ne 'CHANGE_ME'){
	Includes::my_log('task2','info',"Completed by: %s",$username);
}

### Functions ###

sub filter(){
	# Turn off autoescaping to allow XSS puzzle to work
	$c->autoEscape(undef);
	my $val = $c->param('project_name_filter') || '';
	return div({id=>'filter'},
			start_form("POST"),
			fieldset(
				legend("Filter",
					p(sprintf('<input type="text" name="project_name_filter" value="%s" />',$val)),
					p(hidden('username',$c->cookie('username') || 'CHANGE_ME')),
					p(submit('action','Search'))
					),
				),
			end_form);
}

sub results(){
	my $filter = $c->param('project_name_filter');
	# Replace * with % for SQL
	$filter =~ s/\*/%/g;

	# Count the number of -- in the filter to escape the comment
	my $count;
	while($filter =~ /--/g){$count++}

	# Put the query together from the user input
	my $query = 
		sprintf("SELECT name,site FROM projects WHERE%s classification = 'Public'%s",
				$filter? sprintf(' name LIKE \'%s\' AND',$filter):'',
				# If there are an odd number of -- we need another
				$count > 0 && $count % 2? '--':'');
	my @rows = &get_rows($query);
	$SUCCESS = $#rows > 0;

	return
		comment($query),"\n",
		div({class=>"center"},
		table({id=>'results'},
				Tr(
					[
					th(['Name','Site']),
					@rows
					]
					)));
}

# Get the rows from the query {{{
sub get_rows($$){
	my ($query) = @_;
	my @rows;
	my $dbh = Includes::connect($DBFILE);
	# Break out if the SQL doesn't work
	my $sth = $dbh->prepare($query) or return;
	$sth->execute;
	# Push the td's of the rows together
	while( my ($name,$site) = $sth->fetchrow_array){
		my $link = sprintf("%s?path=%s","project_details.cgi",$name);
		push(@rows,td([a({href=>$link},$name),$site]));
	}
	return @rows;
} #}}}
